package com.njwb.azqzb.job.Util;

import com.njwb.azqzb.job.pojo.HoseholdUsers;
import com.njwb.azqzb.job.Driver;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

public class HoseholdDao {

    public ArrayList<HoseholdUsers> findAllHouseholdUsers() throws SQLException {
        String sql = "SELECT Hotel.name AS hotel_name, Hotel.place AS hotel_place, " +
                "Users.name AS user_name, Users.froms AS user_country, " +
                "Household.sdate, Household.edate " +
                "FROM Household " +
                "JOIN Hotel ON Household.hid = Hotel.id " +
                "JOIN Users ON Household.uid = Users.id";

        ArrayList<HoseholdUsers> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                list.add(new HoseholdUsers(
                        rs.getString("hotel_name"),
                        rs.getString("hotel_place"),
                        rs.getString("user_name"),
                        rs.getDate("sdate"),
                        rs.getDate("edate")
                ));
            }
        }
        return list;
    }

    public ArrayList<HoseholdUsers> findHouseholdUsersByHotelName(String hotelName) throws SQLException {
        String sql = "SELECT Hotel.name AS hotel_name, Hotel.place AS hotel_place, " +
                "Users.name AS user_name, Users.froms AS user_country, " +
                "Household.sdate, Household.edate " +
                "FROM Household " +
                "JOIN Hotel ON Household.hid = Hotel.id " +
                "JOIN Users ON Household.uid = Users.id " +
                "WHERE Hotel.name = ?";

        ArrayList<HoseholdUsers> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, hotelName);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                list.add(new HoseholdUsers(
                        rs.getString("hotel_name"),
                        rs.getString("hotel_place"),
                        rs.getString("user_name"),
                        rs.getDate("sdate"),
                        rs.getDate("edate")
                ));
            }
        }
        return list;
    }

    public ArrayList<String> findAllHotelNames() throws SQLException {
        String sql = "SELECT name FROM Hotel";
        ArrayList<String> list = new ArrayList<>();

        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                list.add(rs.getString("name"));
            }
        }
        return list;
    }

    public boolean addHouseholdUser(String hname, String uname, String froms, Date sdate) throws SQLException {
        try (Connection conn = Driver.getConnection()) {
            conn.setAutoCommit(false);  // 事务开始

            // 查询 hotel.id
            String hotelSql = "SELECT id FROM hotel WHERE name = ?";
            int hid = -1;
            try (PreparedStatement ps = conn.prepareStatement(hotelSql)) {
                ps.setString(1, hname);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    hid = rs.getInt("id");
                } else {
                    return false;
                }
            }

            // 插入 users
            String insertUserSql = "INSERT INTO users (name, froms) VALUES (?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertUserSql)) {
                ps.setString(1, uname);
                ps.setString(2, froms);
                ps.executeUpdate();
            }

            // 查询 users.id
            int uid = -1;
            String userSql = "SELECT id FROM users WHERE name = ? AND froms = ? ORDER BY id DESC LIMIT 1";
            try (PreparedStatement ps = conn.prepareStatement(userSql)) {
                ps.setString(1, uname);
                ps.setString(2, froms);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    uid = rs.getInt("id");
                } else {
                    conn.rollback();
                    return false;
                }
            }

            // 插入 household
            String householdSql = "INSERT INTO household (hid, uid, sdate) VALUES (?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(householdSql)) {
                ps.setInt(1, hid);
                ps.setInt(2, uid);
                ps.setDate(3, new java.sql.Date(sdate.getTime()));
                ps.executeUpdate();
            }

            conn.commit();
            return true;
        }
    }
}
